# -*- coding: utf-8 -*-


'''
@author: José Gorgulho 5622
@date: 11/05/2012
@obs
Module containing class for interacting with sqlite3 databases.
Inicially intended only for a Programming Languages Class work

'''

import sqlite3

class sqlite_handler():
    ''' Class for interacting with excell xls files. '''
    
        
    def __init__(self, database_name=None):
        '''Class constructor

        Keyword arguments:
        database_name -- database to work on (default 'None')
                
        '''
        self.database_name = database_name + ".sqlite3"
        self.error_message = ' '
        self.tables = []
        pass
    
    def open_database(self):
        '''Function to open a database

        Keyword arguments:
        database_name -- database to work on (default 'None')

        Returns:
        False - if couldn't open database
                
        '''
        try:
            conn = sqlite3.connect(self.database_name)
        except:
            self.error_message = 'Could\'t open database ' + self.database_name + '.sqlite3 .'
            return False
        cursor = conn.cursor()

        self.conn = conn
        self.cursor = cursor
        pass
    
    def search_table(self, table_name=None, criteria=None):
        '''Function to search on a table

        Keyword arguments:
        table_name -- table to search on(Default 'None')
        criteria -- keyword to search (Default 'None')
        
        Returns:
        False - if couldn't find table
                
        '''

        pass

    def add_to_table(self, table_name=None, list_to_add=None):
        '''Function to search on a table

        Keyword arguments:
        table -- table to search on(Default 'None')
        list_to_add -- list to add to table(Default 'None')
        
        Returns:
        False - if couldn't find table
                
        '''
        
        for x in xrange(0,len(list_to_add)-1):
            sql = '''insert into  ''' + table_name +'''(id,name) values ("{0}","{1}")'''.format(list_to_add[x][1],list_to_add[x][0].encode('utf-8'))
            try:
                self.cursor.execute(sql)
                self.conn.commit()
            except:
                self.error_message = 'Could\'t add fields starting at ' + str(list_to_add[x][1]) + ' and ' + list_to_add[x][0].encode('utf-8') + '. Sql - ' + sql
                return False
        pass
    
    def create_table(self, table_name=None):
        '''Function to create a table

        Keyword arguments:
        table_name -- table to search on(Default 'None')
        
        Returns:
        False - if couldn't create table
                
        '''
        if(table_name == 'schools'):
            sql = 'create table if not exists "' + table_name + '" (id integer, name text)'
            try:
                self.cursor.execute(sql)
                self.conn.commit()
            except:
               self.error_message = 'Could\'t create ' + table_name + ' table. With "' + sql + '"'
               return False
            self.tables.append(table_name)
        pass

    def close_database(self):
        self.cursor.close()
        pass
    pass
